package com.sxfq.loansupermarket.backsystem.modular.refunds.controller;

import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.SearchPaymentTotalInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DepartmentInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/costDetails")
public class CostDetailsController extends BaseController {
    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/balance/";

    /**
     * 跳转费用明细页面
     */
    @RequestMapping(value = "")
    public String recharge(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='"+tableHead+"' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList =sqlMapper.selectList(getListSql,TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList=sqlMapper.selectList(getListSql,DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (ShiroKit.getSessionAttr("firstLevelProjectList") == null) {
            String sql = "select a.first_level_project as firstLevelProject from "+ShiroKit.getUser().getPay_service_info()+" a  group by a.first_level_project order by a.id;";
            List<PayServicedInfo> firstLevelProjectList = sqlMapper.selectList(sql, PayServicedInfo.class);
            ShiroKit.setSessionAttr("firstLevelProjectList", firstLevelProjectList);
        }
        if (ShiroKit.getUser().getDoctorInfoList() == null) {
            String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                    " where b.abbreviation='" + ShiroKit.getUser().getAbbreviation() + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
            List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
            ShiroKit.getUser().setDoctorInfoList(doctorInfoList);
        }
        model.addAttribute("doctorList", ShiroKit.getUser().getDoctorInfoList());
        model.addAttribute("departmentList",ShiroKit.getUser().getDepartmentList());
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("firstLevelProjectList", ShiroKit.getSessionAttr("firstLevelProjectList"));
        model.addAttribute("sttleAccountsTime", ShiroKit.getUser().getSttleAccountsTime());
        return PREFIX + "cost_details.html";
    }

    @RequestMapping(value = "/getCostDetailsReport", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, Object>> report(SearchPaymentTotalInfo searchPaymentTotalInfo, String reach) {
        String tableHead = searchPaymentTotalInfo.getAbbreviation();
        String tableHead1 = searchPaymentTotalInfo.getAreaAbbreviation();
        String tableName1 = tableHead + "recharge_info";
        String tableName2 = tableHead + "deal_info";
        String getsql = "select t.id,t.checkout_method from  " + tableName1 + " t " +
                " left join checkout_method_info t1 on t.checkout_method_id=t1.id where 1=1 and t.checkout_method!='积分' and t.checkout_method!='返现抵扣' " ;//0:正常方式，1:积分，2:优惠券 , 3:储值
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            getsql += " and t.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            getsql += " and t.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        getsql += " group by t.checkout_method order by t1.num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(getsql, CheckoutMethodInfo.class);
        String sql = "select * from (select * from (select b.id, a.recharge_time as '结账时间',b.scarchiving as '网电咨询师',b.yellow_number as '黄页编号',ifnull(b.referee,'') as '推荐人',ifnull(c.referraler_person,'') as '转诊人'," +
                "ifnull(c.referraler_personed,'') as '被转诊人',ifnull(c.referraler_time,'') as '转诊时间',ifnull(c.referraler_remarks,'') AS '转诊备注',b.customer_name as '姓名'," +
                " ( " +
                " CASE b.customer_sex " +
                "  WHEN 1 THEN " +
                "   '男' " +
                "  WHEN 2 THEN " +
                "   '女' " +
                "  ELSE " +
                "   '未知' " +
                "  END " +
                "  ) as '性别'," +
                "b.customer_age as '年龄'," +
                "b.medical_record_number as '病历号', b.customer_phone as '电话',a.id2 as '编号', " +
                "concat('A',c.left_up,'B',c.right_up,'C',c.right_down,'D',c.left_down) as '牙位'," +
                " d.department as '科室',c.is_do as isDo, d.first_level_project as '类别'," +
                " d.pay_service_name as '项目'," +
                " (case when c.is_do=5 then c.discount_hide*c.pay_service_num" +
                "       when c.is_do=4 then 0" +
                "       when c.is_do=3 then 0" +
                " end ) as '原价'," +
                " (case when c.is_do=5 then (select sum(folding_pay) from " + tableName2 + " d where d.id2=c.id2 and d.operation_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=5)" +
                "       when c.is_do=1 then (select sum(folding_pay) from " + tableName2 + " d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=1)" +
                "       when c.is_do=2 then 0" +
                "       when c.is_do=4 then (select sum(folding_pay) from " + tableName2 + " d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=4)" +
                "       when c.is_do=3 then 0" +
                "       when c.is_do=6 then 0" +
                " end ) as '总费用'," +
                " (case when c.is_do=5 then (select sum(folding_pay-deduction_amount) from " + tableName2 + " d where d.id2=c.id2 and d.operation_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=5)" +
                "       when c.is_do=4 then (select sum(arrears+service_actual_pay+deposit_amount) from " + tableName2 + " d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=4)" +
                "       when c.is_do=3 then (select sum(-deduction_amount) from " + tableName2 + " d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=3)" +
                " end ) as '财务应收'," +
                " (select sum(service_actual_pay+deposit_amount) from " + tableName2 + " d where d.id=c.id and c.get_time=d.get_time and d.deal_status='已结账') as '现金流'," +
                " (select sum(service_actual_pay+deposit_amount)*(a.equation/100) from " + tableName2 + " d where d.id=c.id and c.get_time=d.get_time and d.deal_status='已结账') as '实到金额'," +
                " (case when c.is_do=3 then 0" +
                "       when c.is_do=4 then 0" +
                "       when c.is_do=5 then ifnull(c.folding_pay/(c.discount_hide*c.pay_service_num),1)" +
                " end ) as '折扣/活动价',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then " +
                        "a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " sum(case when a.checkout_method='返现抵扣' then a.actual_account_balance else 0 end) as '返现抵扣',";
        sql += " (select sum(arrears) from " + tableName2 + " d where d.id2=c.id2 and d.get_time=c.get_time and d.deal_status='已结账') as '欠费'," +
                "a.document_number as '单据号', c.tc_name as 'TC',ifnull(c.tc_assistant,'') as 'TC助理', c.doctor as '医生'," +
                "ifnull(c.nurse,'') as '护士'," +
                " b.one_channel as '一级渠道',b.two_channel as '二级渠道',b.thr_channel as '三级渠道'," +
                " b.one_medium as '一级媒介',b.two_medium as '二级媒介',b.thr_medium as '三级媒介'," +
                " ifnull(c.orthodontic_number,'') as '正畸编号',  " +
                "ifnull(b.first_diagnosis,'') as '初诊地',b.customer_status as '客户状态',a.remarks as '备注', " +
                " c.left_up as leftUp,c.right_up as rightUp,c.right_down as rightDown,c.left_down as leftDown,a.id2 " +
                " from " + tableName2 + " c left join  " + tableName1 + " a on a.deal_id=c.id" +
                " left join "+tableHead1+"pay_service_info d on c.pay_service_id=d.id left join " + tableHead+"customer_info b on c.customer_id=b.id where 1=1 " +
                " and c.deal_status='已结账' ";
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        if (searchPaymentTotalInfo.getDoctor() != null && searchPaymentTotalInfo.getDoctor() != "") {
            sql += " and c.doctor = '" + searchPaymentTotalInfo.getDoctor() + "' ";
        }
        if (searchPaymentTotalInfo.getFieldTc() != null && searchPaymentTotalInfo.getFieldTc() != "") {
            sql += " and c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' ";
        }
        if (searchPaymentTotalInfo.getDepartment() != null && searchPaymentTotalInfo.getDepartment() != "") {
            sql += " and d.department = '" + searchPaymentTotalInfo.getDepartment() + "' ";
        }
        if (searchPaymentTotalInfo.getFirstLevelProject() != null && searchPaymentTotalInfo.getFirstLevelProject() != "") {
            sql += " and d.first_level_project = '" + searchPaymentTotalInfo.getFirstLevelProject() + "' ";
        }
        if (reach != null && reach != "") {
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        sql += " group by a.deal_id order by a.recharge_time desc";
        sql += " ) t ) t";
        sql += " union all select " +
                " '' as id,'' as '结账时间','' as '网电咨询师','' as '黄页编号','' as '推荐人','' as '转诊人','' as '被转诊人','' as '转诊时间','' AS '转诊备注','合计' as '姓名','' as '性别','' as '年龄'," +
                "'' as '病历号','' as '电话','' as '编号','' as '牙位','' as '科室','' as isDo,'' as '类别','' as '项目',0 as '原价',0 as '总费用',0 as '财务应收',0 as '现金流',0 as '实到金额', " +
                " '' as '折扣/活动价',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                //1:开单缴费，2:定金缴费，3:积分缴费，4:储值抵扣﹣，5:储值抵扣+，6:返现抵扣﹣，7:返现抵扣+，8:优惠券抵扣﹣，9:优惠券抵扣+
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' " +
                        "and (a.document_number is not null and a.deal_id is not null " +
                        "and (a.isEarnes=1 or a.isEarnes=5 or a.isEarnes=7 or a.isEarnes=9)) " +
                        "then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " sum(case when a.checkout_method='返现抵扣' " +
                "and (a.document_number is not null and a.deal_id is not null " +
                "and (a.isEarnes=1 or a.isEarnes=3 or a.isEarnes=5 or a.isEarnes=7 or a.isEarnes=9)) " +
                "then a.actual_account_balance else 0 end) as '返现抵扣',";
        sql += " 0 as '欠费'," +
                "'' as '单据号','' as 'TC','' as 'TC助理','' as '医生','' as '护士'," +
                " '' as '一级渠道','' as '二级渠道','' as '三级渠道'," +
                " '' as '一级媒介','' as '二级媒介','' as '三级媒介'," +
                " '' as '正畸编号','' as '初诊地','' as '客户状态','' as '备注'," +
                "'' as leftUp,'' as rightUp,''  as rightDown,'' as leftDown,'' as id2 " +
                " from "+tableName2+" c left join " + tableName1 + " a on a.deal_id=c.id  left join "+tableHead1+"pay_service_info d on c.pay_service_id=d.id " +
                " left join " +tableHead+"customer_info b on a.customer_id=b.id where 1=1 ";
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        if (searchPaymentTotalInfo.getDoctor() != null && searchPaymentTotalInfo.getDoctor() != "") {
            sql += " and c.doctor = '" + searchPaymentTotalInfo.getDoctor() + "' ";
        }
        if (searchPaymentTotalInfo.getFieldTc() != null && searchPaymentTotalInfo.getFieldTc() != "") {
            sql += " and c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' ";
        }
        if (searchPaymentTotalInfo.getDepartment() != null && searchPaymentTotalInfo.getDepartment() != "") {
            sql += " and d.department = '" + searchPaymentTotalInfo.getDepartment() + "' ";
        }
        if (searchPaymentTotalInfo.getFirstLevelProject() != null && searchPaymentTotalInfo.getFirstLevelProject() != "") {
            sql += " and d.first_level_project = '" + searchPaymentTotalInfo.getFirstLevelProject() + "' ";
        }
        if (reach != null && reach != "") {
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        List<Map<String, Object>> list = sqlMapper.selectList(sql);
        return list;
    }

    @RequestMapping(value = "/export", method = RequestMethod.POST)
    @ResponseBody
    public List<LinkedHashMap<String, Object>> export(SearchPaymentTotalInfo searchPaymentTotalInfo, String reach) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "recharge_info";
        String tableName2 = tableHead + "deal_info";
        String getsql = "select t.id,t.checkout_method from  " + tableName1 + " t " +
                " left join checkout_method_info t1 on t.checkout_method_id=t1.id where 1=1 and t.checkout_method!='积分' and t.checkout_method!='返现抵扣' " ;//0:正常方式，1:积分，2:优惠券 , 3:储值
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            getsql += " and t.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            getsql += " and t.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        getsql += " group by t.checkout_method order by t1.num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(getsql, CheckoutMethodInfo.class);
        String sql = "select * from (select * from (select a.recharge_time as '结账时间',b.customer_name as '姓名'," +
                "( " +
                " CASE b.customer_sex  " +
                "  WHEN 1 THEN  " +
                "   '男'  " +
                "  WHEN 2 THEN  " +
                "   '女'  " +
                "  ELSE  " +
                "   '未知'  " +
                "  END  " +
                "  ) as '性别',b.customer_age as '年龄',b.medical_record_number as '病历号', b.customer_phone as '电话'," +
                "concat('A',c.left_up,'B',c.right_up,'C',c.right_down,'D',c.left_down) as '牙位'," +
                "d.department as '科室',d.first_level_project as '类别',d.pay_service_name as '项目'," +
                " (case when c.is_do=5 then c.discount_hide*c.pay_service_num" +
                "       when c.is_do=4 then 0" +
                "       when c.is_do=3 then 0" +
                " end ) as '原价'," +
                "(case when c.is_do=5 then (select sum(folding_pay-deduction_amount) from " + tableName2 + " d where d.id2=c.id2 and d.operation_time=d.get_time and d.deal_status='已结账' " +
                "  and is_do=5) " +
                "         when c.is_do=4 then (select sum(arrears+service_actual_pay+deposit_amount) from " + tableName2 +" d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账' " +
                "  and is_do=4) " +
                "         when c.is_do=3 then (select sum(-deduction_amount) from " + tableName2 +" d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账' " +
                "  and is_do=3) " +
                "   end ) as '财务应收'," +
                " (case when c.is_do=5 then (select sum(folding_pay) from " + tableName2 + " d where d.id2=c.id2 and d.operation_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=5)" +
                "       when c.is_do=1 then (select sum(folding_pay) from " + tableName2 + " d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=1)" +
                "       when c.is_do=2 then 0" +
                "       when c.is_do=4 then (select sum(folding_pay) from " + tableName2 + " d where d.id2=c.id2 and c.get_time=d.get_time and d.deal_status='已结账'" +
                "                and is_do=4)" +
                "       when c.is_do=3 then 0" +
                "       when c.is_do=6 then 0" +
                " end ) as '总费用'," +
                " " +
                " (select sum(service_actual_pay+deposit_amount) from " + tableName2 + " d where d.id=c.id and c.get_time=d.get_time and d.deal_status='已结账') as '现金流'," +
                " (select sum(service_actual_pay+deposit_amount)*(a.equation/100) from " + tableName2 + " d where d.id=c.id and c.get_time=d.get_time and d.deal_status='已结账') as '实到金额'," +
                " (case when c.is_do=3 then 0" +
                "       when c.is_do=4 then 0" +
                "       when c.is_do=5 then ifnull(c.folding_pay/(c.discount_hide*c.pay_service_num),1)" +
                " end ) as '折扣/活动价'," +
                " (case when c.is_do=3 then '还欠款'" +
                "       when c.is_do=4 then '退款'" +
                "       when c.is_do=5 then '消费'" +
                "       when c.is_do=9 then '储值变动'" +
                " else '其他' end) as 消费类型,";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then " +
                        "a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " sum(case when a.checkout_method='返现抵扣' then a.actual_account_balance else 0 end) as '返现抵扣',";
        sql += " (select sum(arrears) from " + tableName2 + " d where d.id2=c.id2 and d.get_time=c.get_time and d.deal_status='已结账') as '欠费'," +
                "a.document_number as '单据号', c.tc_name as 'TC',ifnull(b.referee,'') as '推荐人',ifnull(c.tc_assistant,'') as 'TC助理', c.doctor as '医生'," +
                "ifnull(c.nurse,'') as '护士'," +
                " b.one_channel as '一级渠道',b.two_channel as '二级渠道',b.thr_channel as '三级渠道'," +
                " b.one_medium as '一级媒介',b.two_medium as '二级媒介',b.thr_medium as '三级媒介'," +
                " b.scarchiving as '网电咨询师',b.yellow_number as '黄页编号',ifnull(c.orthodontic_number,'') as '正畸编号',  " +
                "ifnull(b.first_diagnosis,'') as '初诊地',b.customer_status as '客户状态',ifnull(c.referraler_person,'') as '转诊人',ifnull(c.referraler_personed,'') as '被转诊人'," +
                "ifnull(c.referraler_time,'') as '转诊时间',ifnull(c.referraler_remarks,'') AS '转诊备注',a.remarks as '备注', a.id2 as '编号' " +
                " from " + tableName2 + " c left join  " + tableName1 + " a on a.deal_id=c.id" +
                " left join "+ShiroKit.getUser().getPay_service_info()+" d on c.pay_service_id=d.id left join " + ShiroKit.getUser().getCustomer_info() + " b on c.customer_id=b.id where 1=1 " +
                " and c.deal_status='已结账' ";
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        if (searchPaymentTotalInfo.getDoctor() != null && searchPaymentTotalInfo.getDoctor() != "") {
            sql += " and c.doctor = '" + searchPaymentTotalInfo.getDoctor() + "' ";
        }
        if (searchPaymentTotalInfo.getFieldTc() != null && searchPaymentTotalInfo.getFieldTc() != "") {
            sql += " and c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' ";
        }
        if (reach != null && reach != "") {
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        sql += " group by a.deal_id ";
        sql += " ) t where 1=1 order by t.结账时间 desc) t";
        sql += " \n union all select " +
                " '' as '结账时间','合计' as '姓名','' as '性别','' as '年龄','' as '病历号','' as '电话','' AS '牙位','' as '科室','' as '类别','' as '项目'," +
                "'' as '原价','' as '财务应收','' as '总费用','' as '现金流','' as '实到金额','' as '折扣/活动价','' as '消费类型',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                //1:开单缴费，2:定金缴费，3:积分缴费，4:储值抵扣﹣，5:储值抵扣+，6:返现抵扣﹣，7:返现抵扣+，8:优惠券抵扣﹣，9:优惠券抵扣+
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' " +
                        "and (a.document_number is not null and a.deal_id is not null " +
                        "and (a.isEarnes=1 or a.isEarnes=5 or a.isEarnes=7 or a.isEarnes=9)) " +
                        "then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " sum(case when a.checkout_method='返现抵扣' " +
                "and (a.document_number is not null and a.deal_id is not null " +
                "and (a.isEarnes=1 or a.isEarnes=3 or a.isEarnes=5 or a.isEarnes=7 or a.isEarnes=9)) " +
                "then a.actual_account_balance else 0 end) as '返现抵扣',";
        sql += " '' as '欠费'," +
                "'' as '单据号','' as 'TC','' as '推荐人','' as 'TC助理','' as '医生','' as '护士'," +
                " '' as '一级渠道','' as '二级渠道','' as '三级渠道'," +
                " '' as '一级媒介','' as '二级媒介','' as '三级媒介'," +
                " '' as '网电咨询师','' as '黄页编号','' as '正畸编号','' as '初诊地','' as '客户状态'," +
                " '' as '转诊人','' as '被转诊人','' as '转诊时间','' as '转诊备注','' as '备注','' as '编号' " +
                " from "+tableName2+" c left join " + tableName1 + " a on a.deal_id=c.id  " +
                " left join " + ShiroKit.getUser().getCustomer_info() + " b on a.customer_id=b.id where 1=1 ";
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        if (searchPaymentTotalInfo.getDoctor() != null && searchPaymentTotalInfo.getDoctor() != "") {
            sql += " and c.doctor = '" + searchPaymentTotalInfo.getDoctor() + "' ";
        }
        if (searchPaymentTotalInfo.getFieldTc() != null && searchPaymentTotalInfo.getFieldTc() != "") {
            sql += " and c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' ";
        }
        if (reach != null && reach != "") {
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        List<LinkedHashMap<String, Object>> list = sqlMapper.selectListLink(sql);
        return list;
    }

    //导出
    @RequestMapping(value = "/exportCostDetailedTable")
    public void exportCostDetailedTable(@RequestParam(required = false) String startTime,
                                        @RequestParam(required = false) String endTime,
                                        @RequestParam(required = false) String reach,
                                        @RequestParam(required = false) String fieldTc,
                                        @RequestParam(required = false) String doctor,
                                        @RequestParam(required = false) String customerStatus,HttpServletResponse response) {
        try {
            SearchPaymentTotalInfo info= new SearchPaymentTotalInfo();
            info.setStartTime(startTime);
            info.setEndTime(endTime);
            info.setReach(reach);
            info.setFieldTc(fieldTc);
            info.setDoctor(doctor);
            info.setCustomerStatus(customerStatus);
            List<LinkedHashMap<String, Object>> list = this.export(info,reach);

            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
//            if("1".equals(pageType)){
            for (int i = 0; i < 50; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell;
            for (int i = 0;  i< list.size(); i++) {
                Map<String, Object> map = list.get(i);
                int a=0;
                for (String key : map.keySet()) {
                    cell = row.createCell(a);
                    cell.setCellValue(key);
                    cell.setCellStyle(style);
                    a=a+1;
                }
            }
            // 填充数据
            for (int i = 0;  i< list.size(); i++) {
                Map<String, Object> map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= map.size(); j++) {
                    row.createCell(j).setCellStyle(style);
                }
                int a=0;
                for (String key : map.keySet()) {
                    if(ShiroKit.getUser().getPhonePrivilegesexp()!=1 && "电话".equals(key)) {
                        row.getCell(a).setCellValue("无");
                    }else {
                        row.getCell(a).setCellValue(map.get(key).toString());
                    }
                    a=a+1;
                }
                   /* //System.out.println(k + " : " + m.get(k));*/
//                    row.getCell(1).setCellValue(m.get(k).toString());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}